package com.onede4.db;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Wrap for QueryRunner of apache DBUtils
 * 
 * @author xiaohuiq@hotmail.com
 * 
 */
public class DBUtilsExt {

	private final static QueryRunner runner = new QueryRunner();

	private final static Log log = LogFactory.getLog(DBUtilsExt.class);

	/**
	 * Execute an SQL INSERT, UPDATE, or DELETE query.
	 * 
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            The query replacement parameters.
	 * @return The number of rows updated.
	 */
	public static int update(String sql, Object[] params) {
		try {
			return runner.update(DB.getConnection(), sql, params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
	 * 
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            An array of query replacement parameters. Each row in this
	 *            array is one set of batch replacement values.
	 * @return The number of rows updated per statement.
	 */
	public static int[] batch(String sql, Object[][] params) {
		try {
			return runner.batch(DB.getConnection(), sql, params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * Query an Object
	 * 
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            The query replacement parameters.
	 * @return
	 */
	public static Object findScalar(String sql, Object[] params) {
		try {
			return runner.query(DB.getConnection(), sql, new ScalarHandler(),
					params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * Query a javaBean of T type
	 * 
	 * @param <T>
	 *            The type of object that returns
	 * @param beanclass
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            The query replacement parameters.
	 * @return
	 */
	public static <T> T find(Class<T> beanclass, String sql, Object[] params) {
		try {
			return (T) runner.query(DB.getConnection(), sql,
					new BeanHandler<T>(beanclass), params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * Query a javaBean list
	 * 
	 * @param <T>
	 *            The type of object that returns
	 * @param beanClass
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            The query replacement parameters.
	 * @return
	 */
	public static <T> List<T> findAll(Class<T> beanClass, String sql,
			Object[] params) {
		try {
			return runner.query(DB.getConnection(), sql,
					new BeanListHandler<T>(beanClass), params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * Query an page list
	 * 
	 * @param <T>
	 *            The type of object that returns
	 * @param beanClass
	 * @param sql
	 *            The SQL to execute.
	 * @param pageno
	 *            Page number
	 * @param pagesize
	 *            Page size
	 * @param params
	 *            The query replacement parameters.
	 * @return
	 */
	public static <T> List<T> findSlice(Class<T> beanClass, String sql,
			int pageno, int pagesize, Object[] params) {

		int startindex = (pageno - 1) * pagesize;

		if (startindex < 0) {
			log.info("Exception:::startindex<0");
			startindex = 0;
		}

		if (pagesize < 0) {
			log.info("Exception:::pagesize<0");
			pagesize = Integer.MAX_VALUE;
		}

		return findAll(beanClass, sql + " LIMIT ?,?", ArrayUtils.addAll(params,
				new Integer[] { startindex, pagesize }));
	}

	/**
	 * Query column list
	 * 
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            The query replacement parameters.
	 * @return
	 */
	public static List<Object> findColumnList(String sql, Object[] params) {
		try {
			return runner.query(DB.getConnection(), sql,
					new ColumnListHandler(), params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * Query a map
	 * 
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            The query replacement parameters.
	 * @return
	 */
	public static Map<String, Object> findMap(String sql, Object[] params) {
		try {
			return runner.query(DB.getConnection(), sql, new MapHandler(),
					params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * Query a map list
	 * 
	 * @param sql
	 *            The SQL to execute.
	 * @param params
	 *            The query replacement parameters.
	 * @return
	 */
	public static List<Map<String, Object>> findAllMap(String sql,
			Object[] params) {
		try {
			return runner.query(DB.getConnection(), sql, new MapListHandler(),
					params);
		} catch (SQLException e) {
			throw new DBException(e);
		}
	}

	/**
	 * Query a map list page
	 * 
	 * @param sql
	 *            The SQL to execute.
	 * @param pageno
	 *            Page number
	 * @param pagesize
	 *            Page size
	 * @param params
	 *            The query replacement parameters.
	 * @return
	 */
	public static List<Map<String, Object>> findSliceMap(String sql,
			int pageno, int pagesize, Object[] params) {
		int startindex = (pageno - 1) * pagesize;
		if (startindex < 0) {
			log.info("Exception:::startindex<0");
			startindex = 0;
		}

		if (pagesize < 0) {
			log.info("Exception:::pagesize<0");
			pagesize = Integer.MAX_VALUE;
		}

		return findAllMap(sql + " LIMIT ?,?", ArrayUtils.addAll(params,
				new Integer[] { startindex, pagesize }));
	}

}
